PHASE8: Build Waterway Geometries for Objective Rivers

PHASE8: Build Waterway Geometries for Objective Rivers

Summary
PHASE7 has created cluster-based graph -- with traversal cluster as node and traversal cluster links as edges.
Using previous phases, this phase accomplishes following:

  1. Loads geo-locations of the paths of all objective rivers
  2. Taking each point at 150m distance on source river, find cost-effective path to the destination river
  3. Applies geographical intersections to identify loops and eliminates them

STEP12i: Identifying waterway geometetries

India Major Rivers (When coming from North-to-South):
Ravi River: Kangra to Harike Lake
Beas River: Chamba to Kathlaur
Sutlej River: Rekong Peo to Firozpur
Yamuna River: From Himalya to Prayagraj
Ganga River: From Himalya to Bay of Bengal
Narmada River: From Amarkantak to Arabic Sea
Mahanadhi River: From Dudwa to Pardip
Godavari River: From Trimbak to Mummidivaram
Krishna River: From Mahabaleshwar to Machilipatnam
Kaveri River: Talakaveri to Kaveripattinam
Kandaleru Reservoir: Source of Telugu-Ganga Kandaleru-Poondi Canal
Poondi Reservoir: Destination of Telugu-Ganga Kandaleru-Poondi Canal

NOTE:
This and next two way-to-way steps are completed at this stage -- so that any on-the-fly traversal cross-link generated will be accounted in delooping/intesections.

Getting OpenStreetMap Relation-Ids for Rivers:
Ref: https://wiki.openstreetmap.org/wiki/Overpass_turbo/Wizard
https://gis.stackexchange.com/questions/98371/getting-shapefile-of-river-from-openstreetmap

  <relation id="1236345"
  <tag k="destination" v="Indian Ocean"/>
  <tag k="name" v="Ganga"/>
  <tag k="type" v="waterway"/>
  <tag k="waterway" v="river"/>
  <relation id="1236345"
  <tag k="destination" v="Indian Ocean"/>
  <tag k="name" v="Ganga"/>
  <tag k="type" v="waterway"/>
  <tag k="waterway" v="river"/>

NOTE:
The "member" entries in file are sub-elements. Only the "tag" entries are relevent to us

For way based link (e-g., https://www.openstreetmap.org/way/164799202):
Some waterways -- such as reservoir, may be identified as "way" -- not as "relation"
In this case the xml content will look like:

 <way id="164799202" visible="true" version="9" changeset="168221120" timestamp="2025-06-28T10:31:50Z" user="vaibhavnz" uid="4164103">
  <nd ref="1764080025"/>
  <nd ref="1764080028"/>
 <way id="164799202" visible="true" version="9" changeset="168221120" timestamp="2025-06-28T10:31:50Z" user="vaibhavnz" uid="4164103">
  <nd ref="1764080025"/>
  <nd ref="1764080028"/>

The same way, extract relation/way ids and download xml file for all other rivers and reservoirs

Downloading Shape-file for Relation:
Go to: https://overpass-turbo.eu/
Pan to India
WARNING If you didn't pan to India, the subsequent queries would be scoped to current view and would fail

Click Wizard
Query:
type:relation and id:164799202
Or (slower query):
boundary=administrative and type=boundary and name="Mainland India"
Build and Run Query
<<runs for long time >>
Export
Data | GeoJSON : C:\GangaToKaveri\qgis\osm_turbo_india_geom_164799202_export.geojson

NOTE:
For "way" based waterway -- such as reservoir, use the query:
type:way and id:164799202

Click Wizard
Query:
name="Ganga" and waterway=river and type=waterway and destination="Indian Ocean"
Or:
type:relation and id:1236345
Build and Run Query
Export
As GeoJSON: C:\GangaToKaveri\qgis\osm_turbo_ganges_geom_1236345_export.geojson

Same way, download geojson files for all other relations/ways

STEP12j: Loading geojson file to database (populating way_geometry, and way_geometry_points)

Loading India Boundary Scope -- populating the g2k_graph_scope_boundary table
NOTE: India Boundary Scope will be checked to include the way geometry -- to avoid way-to-way solutions starting or ending outside India
COPY %GEO_FILE% %GEO_FILE%.orig
EDIT %GEO_FILE%
Remove ALL '"type": "Feature"' blocks containing geometry of type OTHER THAN Polygon
E-g., blocks to remove:

    {
      "type": "Feature",
      "properties": {
        "@id": "node/16173236",
        "@relations": [
          {
            "role": "admin_centre",
            "rel": 17453159,
            "reltags": {
              "boundary": "administrative",
              "name": "Mainland India",
              "type": "boundary"
            }
          }
        ]
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          77.2090057,
          28.6138954
        ]
      },
      "id": "node/16173236"
    },
    {
      "type": "Feature",
      "properties": {
        "@id": "node/16173236",
        "@relations": [
          {
            "role": "admin_centre",
            "rel": 17453159,
            "reltags": {
              "boundary": "administrative",
              "name": "Mainland India",
              "type": "boundary"
            }
          }
        ]
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          77.2090057,
          28.6138954
        ]
      },
      "id": "node/16173236"
    },

NOTE: The non-Polygon geometries need to be removed to contain ONLY Polygon geometry

vi ${PCH_APP_HOME}/run_G2KWayGeometryLoader.sh

SET WAY_TITLE='Ganga River'
SET WAY_SUB_TITLE='From Himalya to Bay of Bengal'
SET GEO_FILE=${G2K_HOME}/qgis/osm_turbo_ganges_geom_1236345_export.geojson
SET WAY_TITLE='Ganga River'
SET WAY_SUB_TITLE='From Himalya to Bay of Bengal'
SET GEO_FILE=${G2K_HOME}/qgis/osm_turbo_ganges_geom_1236345_export.geojson

${PCH_APP_HOME}/run_G2KWayGeometryLoader.sh

Same way load all other geojson files for other rivers/reservoirs,

Below picture shows example waterway geometries of Godavari and Krishna rivers
84f93fe861bd9932ddd26ab90fe69a53.png
Ref: C:\GangaToKaveri\docs\godhakrishnablank.png

Analyze way_geometry_points Counts:
SELECT src_geom.title, src_geom.subtitle, COUNT(src_pts.way_pt_id) AS total_way_points
FROM way_geometry_points AS src_pts
INNER JOIN way_geometry AS src_geom ON src_pts.way_id=src_geom.way_id
GROUP BY src_geom.title, src_geom.subtitle;

Results:
     title      |                   subtitle                   | total_way_points
----------------+----------------------------------------------+------------------
 Ganga River    | From Himalya to Bay of Bengal                |             5276
 Godavari River | From Trimbak to Mummidivaram                 |             9138
 Kaveri River   | Talakaveri to Kaveripattinam                 |             4914
 Krishna River  | From Mahabaleshwar to Machilipatnam          |             7888
 Narmada River  | From Amarkantak to Arabic Sea                |             8041
 Yamuna River   | From Himalya to Prayagraj                    |             7598

Results:
     title      |                   subtitle                   | total_way_points
----------------+----------------------------------------------+------------------
 Ganga River    | From Himalya to Bay of Bengal                |             5276
 Godavari River | From Trimbak to Mummidivaram                 |             9138
 Kaveri River   | Talakaveri to Kaveripattinam                 |             4914
 Krishna River  | From Mahabaleshwar to Machilipatnam          |             7888
 Narmada River  | From Amarkantak to Arabic Sea                |             8041
 Yamuna River   | From Himalya to Prayagraj                    |             7598

WITH point_counts AS (
SELECT src_geom.title, src_geom.subtitle, COUNT(src.way_pt_id) AS total_points
FROM way_geometry_points AS src, way_geometry AS src_geom
WHERE src.way_id=src_geom.way_id
GROUP BY src_geom.title, src_geom.subtitle
)
SELECT title, subtitle, total_points, CEIL((CEIL(total_points/100.0)*10000)/800)*800 AS points_to_search
FROM point_counts;
NOTE:
800 is the fetchBatchSize used below for run_G2KWayToWaySolutionBuilder
The total_points below is used as rowEndAt in calls to run_G2KWayToWaySolutionBuilder

     title      |              subtitle               | total_points | points_to_search     |     With G2Y-and-B2Y
----------------+-------------------------------------+--------------+----------------------+---------------------
 Ganga River    | From Himalya to Bay of Bengal       |         5276 |           530400     |  8140 |    820000
 Yamuna River   | From Himalya to Prayagraj           |         7598 |           760000     |  9881 |    990400
 Narmada River  | From Amarkantak to Arabic Sea       |         8041 |           810400
 Godavari River | From Trimbak to Mummidivaram        |         9138 |           920000
 Krishna River  | From Mahabaleshwar to Machilipatnam |         7888 |           790400
 Kaveri River   | Talakaveri to Kaveripattinam        |         4914 |           500000
Kandaleru Reservoir | Source of Telugu-Ganga Kandaleru-Poondi Canal      |         1467 |           150400
Poondi Reservoir    | Destination of Telugu-Ganga Kandaleru-Poondi Canal |          681 |            70400
     title      |              subtitle               | total_points | points_to_search     |     With G2Y-and-B2Y
----------------+-------------------------------------+--------------+----------------------+---------------------
 Ganga River    | From Himalya to Bay of Bengal       |         5276 |           530400     |  8140 |    820000
 Yamuna River   | From Himalya to Prayagraj           |         7598 |           760000     |  9881 |    990400
 Narmada River  | From Amarkantak to Arabic Sea       |         8041 |           810400
 Godavari River | From Trimbak to Mummidivaram        |         9138 |           920000
 Krishna River  | From Mahabaleshwar to Machilipatnam |         7888 |           790400
 Kaveri River   | Talakaveri to Kaveripattinam        |         4914 |           500000
Kandaleru Reservoir | Source of Telugu-Ganga Kandaleru-Poondi Canal      |         1467 |           150400
Poondi Reservoir    | Destination of Telugu-Ganga Kandaleru-Poondi Canal |          681 |            70400

NOTE:
To use points_to_search of "With G2Y-and-B2Y" in run_G2KWayToWaySolutionBuilder for wayToWayBuildSolutions, pass the "--useOnlyTitles true --yesIReallyWantToDo true"

Use STEP12k for comparing the benefits of delooping at STEP12l

Config Params:
fetchBatchSize can be large (set to 800)
Keep wayToWayNearestClustersForShortestPaths (5) matching to G2K Routing REST Server's "g2krouting.nearestNodesForShortestPaths" entry
wayToWayMaxDstPointsPerSrcPoint=100 means that for each source point 100 nearby destination points will be pathed and solutions saved.
onTheFlyDistanceCapKm is for online service to cap how far-away nodes missing the links will be linked on-the-fly at runtime
It is set to more than 5 times online service value (3.0) -- to link far-away nodes in this offline step

On Linux:

vi ${PCH_APP_HOME}/run_G2KWayToWaySolutionBuilder.sh

SRC_WAY_TITLE='Ganga River'
SRC_WAY_SUB_TITLE='From Himalya to Bay of Bengal'
DST_WAY_TITLE='Yamuna River'
DST_WAY_SUB_TITLE='From Himalya to Prayagraj'
SRC_WAY_TITLE='Ganga River'
SRC_WAY_SUB_TITLE='From Himalya to Bay of Bengal'
DST_WAY_TITLE='Yamuna River'
DST_WAY_SUB_TITLE='From Himalya to Prayagraj'

WARNING Make sure to set title variables wrapped around single-quote -- to get passed correctly to Java program

nohup ${PCH_APP_HOME}/run_G2KWayToWaySolutionBuilder.sh --action wayToWayBuildSolutions --rowBeginAt=0 --rowEndAt=530400 > $PCH_LOG_HOME/nohup.out 2>&1 &

The same way, call for all other different source and destination rivers.

SELECT COUNT(*) FROM way_to_way_solutions;

Results:
 count
--------
 145725

Results:
 count
--------
 145725

~~~~~ Take incremental DB backup ~~~~~

STEP12l: Removing loops in traversal intra-cluster links (populating the table: traversal_cluster_xlnk_deloop_link_highorder2, and adding/updating node_highorder2,edge_highorder2)
This step goes through complete and partial solutions in traversal intra-cluster links in traversal_cluster_cross_links_highorder2 table and identifies geographical loops and removes them
Example of geographical loops over graphical routing:
qwc_routing_with_loops_highlighted.png
Ref: C:\GangaToKaveri\docs\qwc_routing_with_loops_highlighted.png

Run below in six parallel pgsql shells:
SET statement_timeout = 19200000; SET lock_timeout=1200000; SET deadlock_timeout = 12000; SET work_mem TO '2048MB';

SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, -1 AS starting_link_edge_id, 63021110 AS ending_link_edge_id \gset
SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, 63021110 AS starting_link_edge_id, 78516516 AS ending_link_edge_id \gset
SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, 78516516 AS starting_link_edge_id, 94011922 AS ending_link_edge_id \gset
SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, 94011922 AS starting_link_edge_id, -1 AS ending_link_edge_id \gset

CALL g2k_run_remove_traversal_cluster_cross_link_loops_highorder2(:batch_size, :max_rounds::smallint, :start_round_num::smallint, :starting_link_edge_id::bigint, :ending_link_edge_id::bigint); 

SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, -1 AS starting_link_edge_id, 63021110 AS ending_link_edge_id \gset
SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, 63021110 AS starting_link_edge_id, 78516516 AS ending_link_edge_id \gset
SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, 78516516 AS starting_link_edge_id, 94011922 AS ending_link_edge_id \gset
SELECT 250 AS batch_size, 500 AS max_rounds, 1 AS start_round_num, 94011922 AS starting_link_edge_id, -1 AS ending_link_edge_id \gset

CALL g2k_run_remove_traversal_cluster_cross_link_loops_highorder2(:batch_size, :max_rounds::smallint, :start_round_num::smallint, :starting_link_edge_id::bigint, :ending_link_edge_id::bigint);

Call for each range

NOTE:
You may start few sessions (4) and parallelize the calls
Removes ~90000 loops per 20 percentile batch
Removes loops 2 percentile at a time (per transaction), in 20 percentile batches -- completing 100 percentile in 360min
Maximum rounds run for each 2 percentile was < 10

Analyze way-to-way solutions:
SELECT 'Ganga River' AS src_way_title, 'From Himalya to Bay of Bengal' AS src_way_sub_title, 'Kaveri River' AS src_way_title, 'Talakaveri to Kaveripattinam' AS src_way_sub_title \gset
SELECT 79.9776 AS dest_lon, 27.0246 AS dest_lat \gset

SELECT 'Kaveri River' AS src_way_title, 'Talakaveri to Kaveripattinam' AS src_way_sub_title, 'Ganga River' AS dst_way_title, 'From Himalya to Bay of Bengal' AS dst_way_sub_title \gset
SELECT 77.7766 AS dest_lon, 12.1203 AS dest_lat \gset

WITH from_pt_ids AS (
SELECT src.way_pt_id AS src_way_pt_id, ST_AsText(src.way_pt) AS src_pt, ST_SetSRID(ST_MakePoint(:dest_lon, :dest_lat), 4326)::geometry <-> src.way_pt AS src_pt_dist
FROM way_geometry_points AS src, way_geometry AS src_geom
WHERE 1=1
AND src.way_id=src_geom.way_id
AND src_geom.title=:dst_way_title AND src_geom.subtitle=:dst_way_sub_title
ORDER BY ST_SetSRID(ST_MakePoint(:dest_lon, :dest_lat), 4326)::geometry <-> src.way_pt
LIMIT 100
), to_pt_ids AS (
SELECT dst.way_pt_id AS dst_way_pt_id
FROM way_geometry_points AS dst, way_geometry AS dst_geom
WHERE 1=1
AND dst.way_id=dst_geom.way_id
AND dst_geom.title=:src_way_title AND dst_geom.subtitle=:src_way_sub_title
), from_to_pts AS (
SELECT src.src_way_pt_id, dst.dst_way_pt_id
FROM from_pt_ids AS src CROSS JOIN to_pt_ids AS dst
), solns AS (
SELECT wws.solution_id,wws.status,wws.edge_cost,wws.distance,wws.src_way_pt_id,wws.dst_way_pt_id,
ROW_NUMBER() OVER (PARTITION BY wws.edge_cost,wws.distance) AS rownum
FROM way_to_way_solutions AS wws
INNER JOIN from_to_pts ftp ON (wws.src_way_pt_id=ftp.src_way_pt_id AND wws.dst_way_pt_id=ftp.dst_way_pt_id)
WHERE wws.status = 0
)
SELECT solns.solution_id,solns.status,solns.edge_cost,solns.distance,solns.src_way_pt_id,solns.dst_way_pt_id
FROM solns
WHERE solns.rownum=1
ORDER BY solns.edge_cost LIMIT 10;

Ganga-to-Kaveri:
solution_id | status | edge_cost | distance | src_way_pt_id | dst_way_pt_id
-------------+--------+-----------+--------------------+---------------+---------------
89718 | 0 | 21255213 | 3633.5341043139224 | 13381 | 15641
89721 | 0 | 21257996 | 3634.3070853600075 | 13381 | 15652
89720 | 0 | 21262203 | 3635.475545764086 | 13381 | 15666
89724 | 0 | 21263872 | 3635.9392804146946 | 13381 | 15669
89726 | 0 | 21265229 | 3636.3163505974926 | 13381 | 15673
89643 | 0 | 21305158 | 3648.2691792600535 | 13357 | 15641
89607 | 0 | 21305853 | 3648.452239504976 | 13349 | 15641
89587 | 0 | 21306281 | 3648.5811966115707 | 13341 | 15641
89664 | 0 | 21306555 | 3648.6570981980312 | 13365 | 15641
89567 | 0 | 21307202 | 3648.8368985338757 | 13333 | 15641

~~~~~ Take incremental DB backup ~~~~~

The result of this phase is pre-calculated cost-effective paths between rivers that are long distance apart.
Below picture is the example of cost-effective paths researched by G2K from Ganga river to Kaveri river:
g2k_ganga_to_kaveri_route_05132025.png
Ref: C:\GangaToKaveri\docs\g2k_ganga_to_kaveri_route_05132025.png